databasemanagementfandomcom-20200214-history
ODBC: Linking Databases
ODBC ODBC stands for open database connectivity that provides a standardized application programming interface between a managed database system and a ‘front-end’ databasing system. ODBC has been around for many years, since the IBM days and can be used in many different languages and platforms. ODBC is not difficult to implement, and it can provide for many uses of database information when done correctly. This set of protocols is bigger than just Oracle or Microsoft, but is a globally active set of protocols that can be used with most platforms that also use SQL. Within Microsoft there are many different connection options that you can use, and some of them will even build and accept SQL or normal, comma separated spread-sheets. The process of connecting to an ODBC bound data source are as follows when using Oracle and Microsoft Access: (Oracle Developer and Microsoft Access must be installed, and the tsnnames.ora file must be altered in order to use this feature. Make sure the network administrator and the professor have made these provisions before starting this operation.) 1. Create a new MS Access database with a name that is meaningful and save it to your removable media. 2. In the main Access control ribbon, click the ‘External Data’ tab, followed by the ‘More’ drop-down menu then click ‘ODBC Database’. When the dialogue box opens up, click the radio button that says ‘Link to data source’. 3. Once this is complete, you must select the data source that you are trying to link to. Do this by clicking ‘new’, selecting the correct driver for the type of database you are using; in this case select the ‘Oracle in OraClient11x’ driver option. THIS IS VERY IMPORTANT BECAUSE IF IT DOES NOT MATCH THE DATABASE TYPE YOU ARE USING IT WILL NOT FUNCTION. 4. Type a meaningful name for the data connection that you are creating and click finish. 5. A secondary dialogue box will appear and prompt you for the service name, user name and password that your professor has provided. This dialogue box will appear again and the information has to be entered a second time. (THIS IS CORRECT BEHAVIOR FOR THE AUTHENICATION SERVICE) 6. The next step is to select your previously created tables and views in order for MS Access to link to them using the ODBC protocols and the information that you have provided. In this step you need to select all tables that are applicable to your project. 7. The final step before actually creating the reports is to select the primary keys from the tables that you created so that MS Access understands the tables that correspond to each other. These simple steps allow for more customized reports on the data in your database, and also better end-user control of the data. A database should not lock your business into a certain mode of operation, it should be as flexible as possible and ODBC in MS Access lets this flexibility be a possibility. SOURCES http://www.oracle.com/technology/software/products/rdbodbc/htdocs/odbc_rn_3310.html http://office.microsoft.com/en-us/access/HA102755501033.aspx http://en.wikipedia.org/wiki/ODBC